/*
* Description: 20110214, RGM, cambio de nombre a columna 'corn_origin' de 'corns_origins'
*	y sus tablas relacionadas
*
*/

EXECUTE sp_rename N'dbo.corns_origins.corn_origin', N'corn_origin_id', 'COLUMN' 

EXECUTE sp_rename N'dbo.corn_futures.corn_origin', N'corn_origin_id', 'COLUMN' 
EXECUTE sp_rename N'dbo.corn_futures_versions.corn_origin', N'corn_origin_id', 'COLUMN' 
EXECUTE sp_rename N'dbo.corns_types.corn_origin', N'corn_origin_id', 'COLUMN' 


/*
	Cambio de tabla bushels_versions
*/

EXECUTE sp_rename N'dbo.bushels_versions', N'bushels_versions_tmp', 'OBJECT' 

Create table [bushels_versions]
(
	[bushel_version_id] Integer Identity NOT NULL,
	[month_period_version_id] Integer NOT NULL,
	[corn_origin_id] Char(1) NOT NULL,
	[amount] Decimal(18,4) NOT NULL,
Primary Key ([bushel_version_id])
) 
go


Alter table [bushels_versions] add  foreign key([month_period_version_id]) references [months_periods_versions] ([month_period_version_id])  on update no action on delete no action 
Alter table [bushels_versions] add  foreign key([corn_origin_id]) references [corns_origins] ([corn_origin_id])  on update cascade on delete no action 
go

Set quoted_identifier on
go

Set quoted_identifier off
go

TRUNCATE TABLE bushels_versions
INSERT INTO bushels_versions (month_period_version_id, corn_origin_id, amount)
	SELECT bvt.month_period_version_id, co.corn_origin_id, 
			CASE WHEN co.corn_origin_id = 'I'
				THEN bvt.amount
				ELSE 0
				END
		FROM bushels_versions_tmp bvt, corns_origins co
		ORDER BY co.corn_origin_id, bvt.bushel_version_id

SELECT *
	FROM bushels_versions

DROP TABLE bushels_versions_tmp

